﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using IBLL;
using System.Data;
using System.Data.Common;
using MisDLL;

namespace MisBLL
{
    public class BacfDAO : BaseDAO,IBacf
    {

        //进货入库
        public void Insert(IList<MisEntity.Bacf> list)
        {
            DateTime _Date = DateTime.Now;
            MisEntity.SYS0 info = dbContext.SYS0.First<MisEntity.SYS0>(item => item.F1 == '1');

            foreach (MisEntity.Bacf entity in list)
            {
                info.NO_CON += 1;
                MisEntity.Goods _Goods = dbContext.Goods.First<MisEntity.Goods>(item => item.ID == entity.ID);
                //_Goods.SUPP_ID = entity.Supp_id;
                _Goods.SL_last = entity.sl;
                _Goods.Je_last = entity.je;
                _Goods.date_last = _Date;
                _Goods.BATS += 1;
                _Goods.SL0 = _Goods.SL0 + entity.sl;
                _Goods.SL1 = _Goods.SL1 + entity.sl;
                _Goods.JE1 = _Goods.JE1 + entity.je;
                if (entity.shop_id == "02")
                {
                    _Goods.SL2 = _Goods.SL2 + entity.sl;
                }
                else if (entity.shop_id == "03")
                {
                    _Goods.SL3 = _Goods.SL3 + entity.sl;
                }

                if (entity.SLKC > 0)
                {
                    _Goods.date_0 = Convert.ToDateTime("2076-01-01");
                }

                entity.NONO = info.NO_CON;
                entity.PACK = (short)_Goods.PACK;
                entity.UNIT_P = _Goods.UNIT_P;
                entity.date = _Date;
                entity.BAT = (short)_Goods.BATS;
                entity.SLKC = (decimal)_Goods.SL1;
                entity.JEKC = (decimal)_Goods.JE1;
                entity.DAY_JS = _Date;
                entity.TYPE_JS = "";
                entity.ce = 0;
                entity.GOODS = _Goods.GOODS;
                entity.F0 = _Goods.F0;
                dbContext.Bacf.InsertOnSubmit(entity);
            }
            dbContext.SubmitChanges();
        }

        //获取进货查询数据
        public DataTable GetEnquiriesAll(string sql, Dictionary<string, object> dictionary)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select bf.no,bf.date,bf.dep_id,bf.supp_id,s.supply,bf.jhtype,bf.id,g.goods,bf.sl,bf.je,bf.shop_id,g.price1,bf.ce,bf.cas,'1900.01.01',bf.cas,0,g.unit,bf.jhtype,bf.no_bei ");
            sb.Append("from bacf_tab bf,goods_tab g,supply_tab s  where bf.id=g.id and bf.supp_id=s.supp_id").Append(sql);
            DbCommand cmd = dbHelper.GetSqlStringCommond(sb.ToString());
            dbHelper.AddParameter(cmd, dictionary);
            return dbHelper.ExecuteDataTable(cmd);
        }

        //获取进货统计数据
        public DataTable GetStatisticsAll(string sql, Dictionary<string, object> dictionary, string tjType)
        {
            StringBuilder sb = new StringBuilder();
            if ("按商场".Equals(tjType))
            {
                sb.Append("select SUBSTRING(M.dep_id,1,2),D.DEPART,sum(M.SL),sum(m.sl*m.price),sum(M.JE) from BACF_tab M,DEPART_TAB D  where");
                sb.Append(sql).Append(" AND SUBSTRING(M.dep_id,1,2)=D.DEP_ID GROUP BY SUBSTRING(M.DEP_ID,1,2),D.DEPART ORDER BY SUBSTRING(M.DEP_ID,1,2)");
            }
            else if ("按部门".Equals(tjType))
            {
                sb.Append("select SUBSTRING(M.dep_id,1,4),D.DEPART,sum(M.SL),sum(m.sl*m.price),sum(M.JE) from bacf_tab M,DEPART_TAB D  where");
                sb.Append(sql).Append(" AND SUBSTRING(M.dep_id,1,4)=D.DEP_ID GROUP BY SUBSTRING(M.DEP_ID,1,4),D.DEPART order by SUBSTRING(M.DEP_ID,1,4)");
            }
            else if ("按合同号".Equals(tjType))
            {
                sb.Append("select m.con_no,sum(M.SL),sum(m.sl*m.price),sum(M.JE),m.supp_id,s.supply from bacf_tab M,supply_tab s where");
                sb.Append(sql).Append(" and m.supp_id=s.supp_id GROUP BY m.con_no,m.supp_id,s.supply order by m.con_no");
            }
            else if ("按供应商".Equals(tjType))
            {
                sb.Append("select m.supp_id,s.supply,sum(M.SL),sum(m.sl*m.price),sum(M.JE) from bacf_tab M,supply_tab s where");
                sb.Append(sql).Append(" and m.supp_id=s.supp_id GROUP BY m.supp_id,s.supply order by m.supp_id");
            }
            else if ("按柜组".Equals(tjType))
            {
                sb.Append("select M.dep_id,D.DEPART,sum(M.SL),sum(m.sl*m.price),sum(M.JE) from bacf_tab M,DEPART_TAB D where");
                sb.Append(sql).Append(" AND M.DEP_ID=D.DEP_ID GROUP BY M.DEP_ID,D.DEPART order by m.dep_id");
            }
            else if ("按商品".Equals(tjType))
            {
                sb.Append("select M.id,D.GOODS,sum(M.SL),sum(m.sl*m.price),sum(M.JE) from bacf_tab M,GOODS_TAB D where");
                sb.Append(sql).Append(" AND M.ID=D.ID GROUP BY M.ID,D.GOODS order by m.id");
            }
            else
            {
                sb.Append("select M.dep_id,D.DEPART,sum(M.SL),sum(m.sl*m.price),sum(M.JE) from bacf_tab M,DEPART_TAB D where");
                sb.Append(sql).Append(" AND M.DEP_ID=D.DEP_ID GROUP BY M.DEP_ID,D.DEPART order by m.dep_id");
            }
            DbCommand cmd = dbHelper.GetSqlStringCommond(sb.ToString());
            dbHelper.AddParameter(cmd, dictionary);
            return dbHelper.ExecuteDataTable(cmd);
        }

        //获取进退货查询数据
        public DataTable GetJtEnquiriesAll(string sql, Dictionary<string, object> dictionary)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select bf.no,bf.date AS DD,g.dep_id,'',bf.supp_id as SS,'',bf.jhtype,bf.id,g.goods,bf.sl,bf.je,bf.shop_id,g.price1,bf.ce,bf.cas,'1900.01.01',bf.cas,0,'','进',g.unit,s.supply ");
            sb.Append("from bacf_tab bf,goods_tab g,supply_tab s where bf.id=g.id and bf.supp_id=s.supp_id").Append(sql);
            sb.Append(" UNION ALL select bf.no,bf.date AS DD,g.dep_id,'',bf.supp_id as SS,'',bf.jhtype,bf.id,g.goods,-bf.sl,-bf.je,bf.shop_id,g.price1,bf.ce,bf.cas,'1900.01.01',bf.cas,0,'','退',g.unit,s.supply ");
            sb.Append("from Racf_tab bf,goods_tab g,supply_tab s where bf.id=g.id and bf.supp_id=s.supp_id").Append(sql);
            DbCommand cmd = dbHelper.GetSqlStringCommond(sb.ToString());
            dbHelper.AddParameter(cmd, dictionary);
            return dbHelper.ExecuteDataTable(cmd);
        }
    }
}
